import glob
import os
import sys

from PyQt4.QtSql import *
from PyQt4.QtGui import * # QStandardItemModel is in GUI :(
from PyQt4.QtCore import *

BAUD_RATES_MODEL = QStandardItemModel()
TIME_SCALE_MODEL = QStandardItemModel()

CYCLE_INITWAIT, CYCLE_DISCHARGE, CYCLE_DISCHARGE_WAIT, CYCLE_TEMPWATCH, CYCLE_CHARGE, CYCLE_CHARGE_WAIT, CYCLE_END, *_ = range(1, 10)

CHARGING_STATE_CHARGING = 'charging'
CHARGING_STATE_DISCHARGING = 'discharging'
CHARGING_STATE_WAITING = 'waiting'
CHARING_STATE_TEMPWATCH = 'temperature_watching'

OVERLAP_INTERVAL = 25 # should be in graph_view

CREATE_SQL_FILE = 'db_struct.sql'
for rate in (9600, 19200, 57600, 115200):
	item = QStandardItem(str(rate))
	item.setData(rate, Qt.UserRole)
	item.setTextAlignment(Qt.AlignRight)
	BAUD_RATES_MODEL.appendRow(item)
del rate

for val, label in ((1, 'másodperc'), (60, 'perc'), (3600, 'óra')):
	item = QStandardItem(label)
	item.setData(val, Qt.UserRole)
	item.setTextAlignment(Qt.AlignRight)
	TIME_SCALE_MODEL.appendRow(item)

CYCLE_SETTINGS_DISPLAYED = [
	'charge_temp_limit', 'charge_voltage_limit', 'charge_error_temp', 'charge_wait_after',
	'discharge_measure_interval', 'discharge_voltage_limit', 'discharge_wait_after',
]

CYCLE_SETTINGS_SPINBOX = CYCLE_SETTINGS_DISPLAYED + ['measure_wait_before', 'display_time_max']
CYCLE_SETTINGS_COMBO = ['charge_device', 'charge_baud', 'display_time_scale']

EXCEL_HEADER = '''<?xml version="1.0" encoding="UTF-8" ?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <Worksheet ss:Name="Sheet1">
  <Table>'''
EXCEL_FOOTER = '''
 </Table>
 </Worksheet>
</Workbook>
'''
EXCEL_NUMBER = '<Cell><Data ss:Type="Number">{}</Data></Cell>\n'
EXCEL_STRING = '<Cell><Data ss:Type="String">{}</Data></Cell>\n'

class BatteryDb(QSqlDatabase):
	@classmethod
	def addSqlDb(cls, filename, connectionName='battery'):
		db = QSqlDatabase.addDatabase("QSQLITE", connectionName)
		db.setDatabaseName(filename)
		db.open()
		return db

	@classmethod
	def removeSqlDb(cls, connectionName='battery'):
		QSqlDatabase.removeDatabase(connectionName)

class IniTable:
	def __init__(self, db, table, parent=None):
		self.model = QSqlTableModel(parent, db)
		self.model.setTable(table)
		self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
		self.db = db
		self.model.select()
		self.build_indices()

	def build_indices(self):
		self.indices = {}
		for i in range(self.model.rowCount()):
			self.indices[self.model.record(i).value('key')] = i

	def __getitem__(self, attr):
		return self.model.record(self.indices[attr]).value('value')

	def __setitem__(self, attr, value):
		if attr in self.indices:
			idx = self.indices[attr]
			record = self.model.record(idx)
			record.setValue('value', value)
			self.model.setRecord(idx, record)
			# idx = self.model.createIndex(self.indices[attr], self.model.fieldIndex('value'))
			#self.model.setData(idx, value)
		else:
			record = self.model.record()
			record.setValue('key', attr)
			record.setValue('value', value)
			self.model.insertRecord(-1, record)

	def clear(self):
		self.model.clear()
	def submitAll(self):
		self.model.submitAll()

def get_battery_files():
	res = []
	for fn in glob.iglob('data/*.sqlite'):
		try:
			battery_db = BatteryDb.addSqlDb(fn, fn)
			prefs_model = IniTable(battery_db, 'preferences')
			cycles_model = QSqlTableModel(db=battery_db)
			cycles_model.setTable('cycles')
			cycles_model.select()
			row_count = cycles_model.rowCount()
			res.append((
				prefs_model['manufacturer'],
				prefs_model['type'],
				prefs_model['id'],
				prefs_model['capacity'],
				row_count,
				fn,
			))
			del prefs_model, cycles_model, battery_db
			BatteryDb.removeSqlDb(fn)
		except Exception as e: print(repr(e), file=sys.stderr)
	return res

def create_battery_db(manufacturer, db_type, db_id, capacity):
	filename = 'data/{}.sqlite'.format(db_id)
	if os.path.exists(filename):
		raise RuntimeError("File already exists")
	battery_db = BatteryDb.addSqlDb(filename, 'newDb')
	q = QSqlQuery(battery_db)
	error = False
	with open(CREATE_SQL_FILE, 'r') as f:
		for line in f:
			res = q.exec_(line)
			if not res:
				error = True
				print("Executing query {}\nResult: {}\nLast error {}\n{}"
					.format(line, res,
						q.lastError().driverText(), q.lastError().databaseText()))
	if error:
		return None
	prefs_model = IniTable(battery_db, 'preferences')
	prefs_model['manufacturer'] = manufacturer
	prefs_model['type'] = db_type
	prefs_model['id'] = db_id
	prefs_model['capacity'] = str(capacity)
	prefs_model.submitAll()
	del prefs_model, battery_db
	BatteryDb.removeSqlDb('newDb')
	return filename


def scalar_query(query_sql, database):
	query = QSqlQuery(query_sql, database)
	query.first()
	res = query.record().field(0).value()
	if isinstance(res, QPyNullVariant):
		res = None
	return res

def cycle_length(cycle_id, database):
	return scalar_query('SELECT max(ts) from "measure_data" where cycle_id = {}'.format(cycle_id), database)

def rows_in_table(tablename, database):
	return scalar_query('SELECT COUNT(*) from "{}"'.format(tablename), database)

def measure_query(cycle_id, database, min_ts=None, max_ts=None):
	extra_q = ''
	if min_ts is not None:
		extra_q += ' AND ts >= ' + str(min_ts)
	if max_ts is not None:
		extra_q += ' AND ts <= ' + str(max_ts)
	
	query = 'SELECT * FROM "measure_data" WHERE cycle_id = {} {} ORDER BY ts ASC'.format(cycle_id, extra_q)
	return QSqlQuery(query, database)

def export_cycles(filename, cycles_model, battery_db):
	try:
		with open(filename, 'w', encoding='utf-8') as f:
			f.write(EXCEL_HEADER)
			f.write('<Row>\n' + (EXCEL_STRING * 5).format('Time', 'cycle', 'U', 'U2', 'I', 'temp') + '</Row>\n')

			cycle_no = 0
			time_offset = 0.0
			while True:
				record = cycles_model.record(cycle_no)
				cycle_id = record.field('id').value()
				if cycle_id is None:
					break
				cycle_no += 1

				query = measure_query(cycle_id, battery_db)

				rec = query.record() # empty record to figure out field positions
				voltage_idx = rec.indexOf('voltage')
				voltage2_idx = rec.indexOf('voltage2')
				current_idx = rec.indexOf('current')
				temperature_idx = rec.indexOf('temperature')
				ts_idx = rec.indexOf('ts')

				ts = 0.0
				while query.next():
					f.write('<Row>\n')
					ts = query.value(ts_idx)
					for val in ((time_offset + ts) / 60,
							cycle_no,
							query.value(voltage_idx),
							query.value(voltage2_idx),
							query.value(current_idx),
							query.value(temperature_idx)):
						if val is not None and val != '': f.write(EXCEL_NUMBER.format(val))
						else: f.write('<Cell></Cell>\n')
					f.write('</Row>\n')
				time_offset += ts
			f.write(EXCEL_FOOTER)
	except Exception as e:
		QMessageBox(QMessageBox.Critical, 'Hiba', 'Nem sikerült elmenteni a fájlt\n' + repr(e)).exec_()
